﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using zichan.DAL;
using zichan.Entity;
using MvcPaging;
using System.Data.SqlClient;
using System.Data;
using System.Transactions;

namespace zichan.BLL
{
    public class AssetService
    {
        public static List<int> AddAsset(Asset model)
        {
            SqlParameter[] paras = new SqlParameter[10];
            paras[0] = new SqlParameter("@quantity", SqlDbType.Int);
            paras[0].Value = model.Number;
            paras[1] = new SqlParameter("@name", SqlDbType.NVarChar, 100);
            paras[1].Value = model.Name;
            paras[2] = new SqlParameter("@asseteClassId", SqlDbType.Int);
            paras[2].Value = model.AssetClassId;
            paras[3] = new SqlParameter("@companyId", SqlDbType.Int);
            paras[3].Value = model.CompanyId;
            paras[4] = new SqlParameter("@buyerId", SqlDbType.Int);
            paras[4].Value = model.BuyerId;
            paras[5] = new SqlParameter("@buyTime", SqlDbType.Date);
            paras[5].Value = model.BuyTime;
            paras[6] = new SqlParameter("@recipient", SqlDbType.Int);
            paras[6].Value = model.Recipient;

            paras[7] = new SqlParameter("@calculatedGround", SqlDbType.NVarChar);
            if (!string.IsNullOrEmpty(model.CalculatedGround))
                paras[7].Value = model.CalculatedGround;
            else
                paras[7].Value = DBNull.Value;

            paras[8] = new SqlParameter("@supplier", SqlDbType.NVarChar, 100);
            if (!string.IsNullOrEmpty(model.Supplier))
                paras[8].Value = model.Supplier;
            else
                paras[8].Value = DBNull.Value;

            paras[9] = new SqlParameter("@remark", SqlDbType.NVarChar);
            if (!string.IsNullOrEmpty(model.Remark))
                paras[9].Value = model.Remark;
            else
                paras[9].Value = DBNull.Value;
            using (ZichanDBContext db = new ZichanDBContext())
            {
                List<int> ids = db.Database.SqlQuery<int>(@"exec [Pro_addAssets] @quantity,@name,@asseteClassId,@companyId,@buyerId,@buyTime,@recipient,@calculatedGround,@supplier,@remark", paras).ToList();
                return ids;
            }
        }
        public static string AddCodes(string codes,string constr)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {
                SqlConnection sqlconn = new SqlConnection(constr);
                SqlCommand sqlcom = new SqlCommand("Pro_Code",sqlconn);
                sqlcom.CommandType = System.Data.CommandType.StoredProcedure;
                sqlconn.Open();
                SqlParameter[] paras = new SqlParameter[] {new SqlParameter("@splitId","#"),new SqlParameter("@splitCode",","),new SqlParameter("@codes",codes) };
                sqlcom.Parameters.AddRange(paras);
                int err = sqlcom.ExecuteNonQuery();
                if (err == -1)
                {
                    return "添加失败";
                }
                else
                {
                    return string.Empty;
                }
            }
        }
        
        public static string Edit(Asset model)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {
                try
                {
                    if(model.Status==3&&model.AssetClassId==1)
                    {
                        if (db.PutawayServers.Count(o => o.Code == model.Code&&o.Statu==1) > 0)
                            return "该服务器已上架，无法报废";
                    }
                    if (model.Status == 1 && model.AssetClassId == 1)
                    {
                        if (db.PutawayServers.Count(o => o.Code == model.Code&&o.Statu==1) > 0)
                            return "该服务器已上架，无法回收";
                    }
                    db.Entry(model).State = System.Data.Entity.EntityState.Modified;
                    db.SaveChanges();
                    return string.Empty;
                }
                catch (Exception ex)
                {
                    return "失败";
                }
            }
        }
        public static IPagedList<Asset> List(string name, string code, int? assetClassId, int? companyId, DateTime? sdt, DateTime? edt, int pageSize, int pageIndex)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {
                IQueryable<Asset> w = db.Assets.Where(o=>o.Status==0);
                if (!string.IsNullOrEmpty(name))
                    w = w.Where(o => o.Name.Contains(name));
                if (!string.IsNullOrEmpty(code))
                    w = w.Where(o => o.Code.Contains(code));
                if (assetClassId.HasValue)
                    w = w.Where(o => o.AssetClassId == assetClassId);
                if (companyId.HasValue)
                    w = w.Where(o => o.CompanyId == companyId);
                if (sdt.HasValue)
                    w = w.Where(o => o.BuyTime >= sdt);
                if (edt.HasValue)
                {
                    edt = DateTime.Parse(string.Format("{0:yyyy-MM-dd 23:59:59}", edt));
                    w = w.Where(o => o.BuyTime <= edt);
                }
                List<Asset> asset = w.OrderByDescending(o => o.UploadTime).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                List<int> classIds = asset.Select(o => o.AssetClassId).ToList();
                var classNames = db.AsseteClassifies.Where(o => classIds.Contains(o.Id)).Select(o => new { o.Id, o.Name });
                List<int> companyIds = asset.Select(o => o.CompanyId).ToList();
                var companyNames = db.CompanyClasses.Where(o => companyIds.Contains(o.Id)).Select(o => new { o.Id, o.Name });
                List<int> buyerIds = asset.Select(o => o.BuyerId).ToList();
                var buyerNames = db.Staffs.Where(o => buyerIds.Contains(o.Id)).Select(o => new { o.Id, o.Name });
                foreach (Asset m in asset)
                {
                    m.AssetClassName = classNames.Where(o => o.Id == m.AssetClassId).Select(o => o.Name).FirstOrDefault();
                    m.CompanyName = companyNames.Where(o => o.Id == m.CompanyId).Select(o => o.Name).FirstOrDefault();
                    m.BuyerName = buyerNames.Where(o => o.Id == m.BuyerId).Select(o => o.Name).FirstOrDefault();
                    m.IsStorage = 0;
                    if (db.AsseteFields.Count(o => o.AsseteClassId == m.AssetClassId) > 0)
                    {
                        List<int> fields = db.AsseteFields.Where(o => o.AsseteClassId == m.AssetClassId).Select(o => o.Id).ToList();
                        if (db.ConsumateInfos.Count(o => fields.Contains(o.FieldId) && o.AssetId == m.Id) > 0||db.ServerInfos.Count(o=>o.AssetId==m.Id)>0)
                        {
                            m.IsStorage = 1;
                        }
                    }
                    else
                        m.IsStorage = 1;
                }
                return asset.OrderByDescending(o=>o.UploadTime).ToList().ToPagedList(pageIndex - 1, pageSize, w.Count());
            }
        }
        public static string Remove(int id)
        {
            try
            {
                using (ZichanDBContext db = new ZichanDBContext())
                {
                    Asset model = db.Assets.Where(o => o.Status == 0 && o.Id == id).FirstOrDefault();
                    if (model != null)
                    {
                        UsageLog usageLog = new UsageLog();
                        usageLog.AssetClassId = model.AssetClassId;
                        usageLog.AssetId = model.Id;
                        usageLog.Code = model.Code;
                        usageLog.Time = DateTime.Now;
                        usageLog.Operation = "删除";
                        usageLog.Remark = model.Remark;
                        db.Assets.Remove(model);
                        db.UsageLogs.Add(usageLog);
                        db.SaveChanges();
                        
                        return string.Empty;
                    }
                    else
                    {
                        return "无法删除";
                    }
                }
            }
            catch(Exception ex)
            {
                return "无法删除";
            }
        }
        public static Asset Get(int id)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {
                Asset model= db.Assets.Where(o => o.Id == id).FirstOrDefault();
                model.AssetClassName = AsseteClassService.GetAsseteClass(model.AssetClassId).Name;
                model.CompanyName = CompanyClassService.GetCompany(model.CompanyId).Name;
                model.BuyerName = StaffService.Get(model.BuyerId).Name;
                model.RecipientName = StaffService.Get(model.Recipient).Name;
                return model;
            }
        }
        public static string PutBatch(string ids, string constr)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {
                SqlConnection sqlcon = new SqlConnection(constr);
                SqlCommand sqlcom = new SqlCommand("Pro_Put", sqlcon);
                sqlcom.CommandType = System.Data.CommandType.StoredProcedure;
                sqlcon.Open();
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ids", ids), new SqlParameter("@splitChar", ',') };
                sqlcom.Parameters.AddRange(paras);
                int err = sqlcom.ExecuteNonQuery();
                if(err==-1)
                {
                    return "添加失败";
                }
                else
                {
                    return string.Empty;
                }
            }

        }
        public static string DeleteBatch(string ids, string constr)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {
                SqlConnection sqlcon = new SqlConnection(constr);
                SqlCommand sqlcom = new SqlCommand("Pro_Delete", sqlcon);
                sqlcom.CommandType = System.Data.CommandType.StoredProcedure;
                sqlcon.Open();
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ids", ids), new SqlParameter("@splitChar", ',') };
                sqlcom.Parameters.AddRange(paras);
                int err = sqlcom.ExecuteNonQuery();
                if (err == -1)
                {
                    return "删除失败";
                }
                else
                {
                    return string.Empty;
                }
            }

        }
        /// <summary>
        /// 查找资产列表中已出库，未上架的服务器的编码
        /// </summary>
        /// <param name="companyId"></param>
        /// <returns></returns>
        public static List<string> codeList(int companyId)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {
                List<string> PutCodes = db.PutawayServers.Where(o => o.Statu == 1).Select(o => o.Code).ToList();
                List<string> codes = db.Assets.Where(o => o.CompanyId == companyId&&o.Status==2&&o.AssetClassId == 1&&!PutCodes.Contains(o.Code)).Select(o => o.Code).ToList();
                return codes;
            }
        }
        public static List<string> codeList(int id,int companyId)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {
                List<string> PutCodes = db.PutawayServers.Where(o => o.Statu == 1&&o.Id!=id).Select(o => o.Code).ToList();
                List<string> codes = db.Assets.Where(o => o.CompanyId == companyId && o.Status == 2 && o.AssetClassId == 1 && !PutCodes.Contains(o.Code)).Select(o => o.Code).ToList();
                return codes;
            }
        }

        #region //添加旧数据，可删
        public static List<Asset> ServerList(int companyId)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {
               
                List<int> assetId = db.ServerInfos.Select(o => o.AssetId).Distinct().ToList();
                List<string> PutServerId= db.PutawayServers.Where(o => o.Statu == 1).Select(o => o.Code.Substring(o.Code.Length-4,4)).ToList();
                List<Asset> serverId = db.Assets.Where(o => o.CompanyId == companyId && o.Status == 1 && o.AssetClassId == 1 && !PutServerId.Contains(o.Code.Substring(o.Code.Length - 4, 4))).ToList();
               // List<int> serverId = Array.ConvertAll<string, int>(codes.ToArray(), s => int.Parse(s)).ToList();
                return serverId;
            }
        }
        public static List<Asset> ServerList(int id,int companyId)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {

                List<int> assetId = db.ServerInfos.Select(o => o.AssetId).Distinct().ToList();
                List<string> PutServerId = db.PutawayServers.Where(o => o.Statu == 1 && o.Id != id).Select(o => o.Code.Substring(o.Code.Length - 4, 4)).ToList();
                List<Asset> serverId = db.Assets.Where(o => o.CompanyId == companyId && o.Status == 2 && o.AssetClassId == 1 && !PutServerId.Contains(o.Code.Substring(o.Code.Length - 4, 4))).ToList();
                // List<int> serverId = Array.ConvertAll<string, int>(codes.ToArray(), s => int.Parse(s)).ToList();
                return serverId;
            }
        }
        public static int AddOldAsset(Asset model)
        {
            SqlParameter[] paras = new SqlParameter[11];
            paras[0] = new SqlParameter("@iden", SqlDbType.NVarChar);
            paras[0].Value = model.Identitys;
            paras[1] = new SqlParameter("@name", SqlDbType.NVarChar, 100);
            paras[1].Value = model.Name;
            paras[2] = new SqlParameter("@asseteClassId", SqlDbType.Int);
            paras[2].Value = model.AssetClassId;
            paras[3] = new SqlParameter("@companyId", SqlDbType.Int);
            paras[3].Value = model.CompanyId;
            paras[4] = new SqlParameter("@buyerId", SqlDbType.Int);
            paras[4].Value = model.BuyerId;
            paras[5] = new SqlParameter("@buyTime", SqlDbType.Date);
            paras[5].Value = model.BuyTime;
            paras[6] = new SqlParameter("@recipient", SqlDbType.Int);
            paras[6].Value = model.Recipient;

            paras[7] = new SqlParameter("@calculatedGround", SqlDbType.NVarChar);
            if (!string.IsNullOrEmpty(model.CalculatedGround))
                paras[7].Value = model.CalculatedGround;
            else
                paras[7].Value = DBNull.Value;

            paras[8] = new SqlParameter("@supplier", SqlDbType.NVarChar, 100);
            if (!string.IsNullOrEmpty(model.Supplier))
                paras[8].Value = model.Supplier;
            else
                paras[8].Value = DBNull.Value;

            paras[9] = new SqlParameter("@remark", SqlDbType.NVarChar);
            if (!string.IsNullOrEmpty(model.Remark))
                paras[9].Value = model.Remark;
            else
                paras[9].Value = DBNull.Value;
            //paras[10] = new SqlParameter("@purposeId", SqlDbType.Int);
            //paras[10].Value = model.serverPurposeId;
            //paras[11] = new SqlParameter("@subPurposeId", SqlDbType.Int);
            //paras[11].Value = model.serverPurposeSubId;
            paras[10] = new SqlParameter("@userId", SqlDbType.Int);
            paras[10].Value = model.UserId;
            using (ZichanDBContext db = new ZichanDBContext())
            {
                int id =db.Database.SqlQuery<int>(@"exec [Pro_AddoldAssetes] @userId,@name,@asseteClassId,@companyId,@buyerId,@buyTime,@recipient,@calculatedGround,@supplier,@remark,@iden", paras).FirstOrDefault();
                return id;
            }
        }
        public static void DeleteOldAssete(int id)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {
                Asset model = db.Assets.FirstOrDefault(o => o.Id == id);
                db.Assets.Remove(model);
                db.SaveChanges();
            }
        }
        public static int oldCabinetNum(int id)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {
                return db.PutawayServers.Where(o => o.CabinetId == id).FirstOrDefault().Vacancy.Value;
            }
        }
        public static string EditPurpose(int id ,int purposeId,int purposeSubId)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {
                try
                {
                    Asset asset = db.Assets.FirstOrDefault(o => o.Id == id);
                    //asset.serverPurposeId = purposeId;
                    //asset.serverPurposeSubId = purposeSubId;
                    db.SaveChanges();
                    return string.Empty;
                }
                catch (Exception ex)
                {
                    return "无法修改";
                }
            }
        }
        #endregion
    }
}
